﻿Imports System.Data.SqlClient
Public Class dbGL
    Dim Var As New varDeclare

    Public Function GLAccountList(ByVal Comp As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GLAccountList"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Comp
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GLJournalList(ByVal Comp As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GLJournalList"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Comp
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GLJournalDetail(ByVal Comp As String, ByVal JournalID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GLJournalDetail"
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Comp
        command.Parameters.Add("@JournalID", SqlDbType.VarChar).Value = JournalID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function getSeqJournalID(ByVal JournalType As String, ByVal JournalPeriod As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GetSeqJournalID"
        command.Parameters.Add("@JournalType", SqlDbType.VarChar).Value = JournalType
        command.Parameters.Add("@JournalPeriod", SqlDbType.VarChar).Value = JournalPeriod
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function SaveJournalHeaderGL(ByVal JournalIDL As String, ByVal JournalIDN As String, ByVal JournalType As String, _
                                        ByVal JournalPeriod As String, ByVal GLDate As DateTime, ByVal Notes As String, _
                                        ByVal CurrID As String, ByVal CurrRate As Decimal, ByVal CurrBase As String, _
                                        ByVal Debet As Decimal, ByVal Credit As Decimal, ByVal EntryBy As String, _
                                        ByVal CompID As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_SaveJournalHeaderGL"
        command.Parameters.Add("@JournalIDL", SqlDbType.VarChar).Value = JournalIDL
        command.Parameters.Add("@JournalIDN", SqlDbType.VarChar).Value = JournalIDN
        command.Parameters.Add("@JournalType", SqlDbType.VarChar).Value = JournalType
        command.Parameters.Add("@JournalPeriod", SqlDbType.VarChar).Value = JournalPeriod
        command.Parameters.Add("@GLDate", SqlDbType.Date).Value = GLDate
        command.Parameters.Add("@Notes", SqlDbType.VarChar).Value = Notes
        command.Parameters.Add("@CurrID", SqlDbType.VarChar).Value = CurrID
        command.Parameters.Add("@CurrRate", SqlDbType.Decimal).Value = CurrRate
        command.Parameters.Add("@CurrBase", SqlDbType.VarChar).Value = CurrBase
        command.Parameters.Add("@Debet", SqlDbType.Decimal).Value = Debet
        command.Parameters.Add("@Credit", SqlDbType.Decimal).Value = Credit
        command.Parameters.Add("@EntryBy", SqlDbType.VarChar).Value = EntryBy
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function SaveJournalLineGL(ByVal JournalID As String, ByVal GLDate As DateTime, ByVal GLSeq As Integer, _
                                        ByVal GLID As String, ByVal Notes As String, ByVal Debet As Decimal, _
                                        ByVal Credit As Decimal, ByVal Curr As String, ByVal Rate As Decimal, _
                                        ByVal Type As String, ByVal CompID As String) As Boolean
        Dim connection As SqlConnection
        Dim command As New SqlCommand

        Dim x1 As Array = JournalID.Split("-")
        Dim JournalType As String = x1(0).ToString()
        Dim x2 As Array = x1(1).ToString().Split("/")
        Dim JournalDocu As Integer = Convert.ToInt32(x2(0))

        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_SaveJournalLineGL"
        command.Parameters.Add("@JournalType", SqlDbType.VarChar).Value = JournalType
        command.Parameters.Add("@JournalDocu", SqlDbType.Int).Value = JournalDocu
        command.Parameters.Add("@GLDate", SqlDbType.Date).Value = GLDate
        command.Parameters.Add("@GLSeq", SqlDbType.Int).Value = GLSeq
        command.Parameters.Add("@GLID", SqlDbType.VarChar).Value = GLID
        command.Parameters.Add("@Notes", SqlDbType.VarChar).Value = Notes
        command.Parameters.Add("@Debet", SqlDbType.Decimal).Value = Debet
        command.Parameters.Add("@Credit", SqlDbType.Decimal).Value = Credit
        command.Parameters.Add("@Curr", SqlDbType.VarChar).Value = Curr
        command.Parameters.Add("@Rate", SqlDbType.Decimal).Value = Rate
        command.Parameters.Add("@Type", SqlDbType.VarChar).Value = Type
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToBoolean(result)
    End Function

    Public Function deleteJournalGL(ByVal JournalID As String, ByVal CompID As String) As Boolean
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GLJournalDelete"
        command.Parameters.Add("@JournalID", SqlDbType.VarChar).Value = JournalID
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToBoolean(result)
    End Function

    Public Function ListMasterChartOfAccount(Level As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT DISTINCT GLAC_CODE 'GL Account ID', GLAC_DESC 'GL Account Name' FROM GL_ACCOUNT WHERE COMP_CODE = @CompID AND STATUS <> 'I' AND GLAC_LEVEL " + Level
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GenerateCOA(ByVal AccountGroup As String, ByVal CompID As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GenerateCOA"
        command.Parameters.Add("@AccountGroup", SqlDbType.VarChar).Value = AccountGroup
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function COAManagement(ParentID As String, AccountID As String, AccountName As String, AccountType As String, AccountLevel As String, _
                                  UserID As String, Type As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GLAccountManagement"
        command.Parameters.Add("@ParentID", SqlDbType.VarChar).Value = ParentID
        command.Parameters.Add("@AccountID", SqlDbType.VarChar).Value = AccountID
        command.Parameters.Add("@AccountName", SqlDbType.VarChar).Value = AccountName
        command.Parameters.Add("@AccountType", SqlDbType.VarChar).Value = AccountType
        command.Parameters.Add("@AccountLevel", SqlDbType.VarChar).Value = AccountLevel
        command.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@Type", SqlDbType.VarChar).Value = Type
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function
End Class
